/*
 * Database abstraction for handling connection logic
 */
package healthcare;

import java.sql.*;
import java.util.*;


/**
 * Consolidates connection configuration and initiation of connections to database.
 * The code must be able to connect to arbitrary (eg. localhost/towson) databases.
 * 
 * @author Doug Keeley
 */
public class Database {
        
    //private static Connection connection = null;  //for caching
    
    //defaults for dev
    private static String dbDriverClass = "oracle.jdbc.driver.OracleDriver";
    private static String dbDriverPrefix = "jdbc:oracle:thin";
    private static String dbHost = "@localhost";
    private static String dbPort = "1521";
    private static String dbSsid = "XE";
    private static String dbUsername = "hdcp1";
    private static String dbPassword = "h3llo";
    
    /*demonstration database info
    private static String dbDriverClass = "oracle.jdbc.driver.OracleDriver";
    private static String dbDriverPrefix = "jdbc:oracle:thin:";
    private static String dbHost = "@orion.towson.edu";
    private static String dbPort = "1521";
    private static String dbSsid = "cosc";
    private static String dbUsername = "dkeele1";
    private static String dbPassword = "eBXyqQU6";
    */
     
    /**
     * configure database for connection to arbitrary database
     */    
    public static void initialize(String p_dbHost, String p_dbPort, String p_dbUsername, String p_dbPassword) throws Exception {

         //this.dbDriverClass = dbDriverClass;    //assume oracle
        //this.dbDriverPrefix = dbDriverPrefix;  //assume oracle
        dbHost = p_dbHost;
        dbPort = p_dbPort;
        //this.dbSsid = dbSsid;      //assume no database/ssid/tablespace.  May need to set default tablespace for user
        dbUsername = p_dbUsername;
        dbPassword = p_dbPassword;
        
    }
    
    /**
     * test the database connection.
     * This code should be changed to test access to a project-specific table.  
     * - Should code just rely on exceptions to indicate failure/success?  Or both 'true' and not-exception?
     * 
     * @return true if connection was tested ok; false if not
     */
    public static boolean testConnection() throws AppException {
        boolean connectedToDatabase = false;
        boolean connectedToData = false;
        
        try {
            Connection conn = getConnection();
            connectedToDatabase = true;  //redundant??
            
            Statement stmt =  conn.createStatement();
            ResultSet rs = stmt.executeQuery("select count(*) from Client");
            connectedToData = true;

            System.out.println("HdcpDatabase.testDatabaseConnection(): database test succeeded; found correct database");
        }
        catch (Exception e) {    //need to clarify what exception we're looking for - SqlException??
            //connectedOk remains false
            System.out.println("HdcpDatabase.testDatabaseConnection(): database test failed with exception: " + e); 
            throw new AppException("Failed to connect to database");
        } 
        
        return (connectedToDatabase && connectedToData);   
    }
  
    /**
     * get a connection.  
     * Currently this is a new connection, but we can change this to cache the
     * same connection.  The class would have made sense as a singleton for the 
     * Finally() method to close the connection
     * 
     * @return 
     */
    public static Connection getConnection() throws AppException {
        
        Connection connection = null;
        
        try {
            Class.forName(dbDriverClass);  //make sure classpath includes oracle driver (eg: ojdbc6_g.jar)
            String dbConnectionString = dbDriverPrefix + ":" +
                                        dbHost + ":" +
                                        dbPort + ":" +
                                        dbSsid ;
            
            System.out.println("HdcpDatabase.getConnection(): creating connection [" + 
                                        dbConnectionString + ", " + dbUsername + ", " + dbPassword + "]...");
            
            //original code
            //Class.forName("oracle.jdbc.driver.OracleDriver");  //you will get error here if classpath isn't set properly
            //Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521", "doug2","h3llo");  //last params is uname/password

            connection = DriverManager.getConnection(dbConnectionString, dbUsername, dbPassword);
           
            System.out.println("HdcpDatabase.getConnection(): successfully opened connection");
        }
        catch (SQLException e) {  
            System.out.println("HdcpDatabase.getConnection(): failed to create connection. " + e);
            throw new AppException("Failed to connect to database");
        } 
        catch (ClassNotFoundException e) {  
            System.out.println("HdcpDatabase.getConnection(): failed to find driver class [" + dbDriverClass + "] " + e);
            throw new AppException("JDBC driver not found");
        } 
        return connection;
        
    }  //getConnection()
    

    
    public static void insertClient(Client client) throws AppException {

        try {
            Connection conn = getConnection();
        
            String sql = "insert into client(CLIENTSSN, " +
                                        "FIRSTNAME, " + 
                                        "MIDDLEINIT, " + 	
                                        "LASTNAME, " + 		
                                        "STREET, " + 		
                                        "CITY, " + 			
                                        "STATE, " + 		
                                        "PHONE, " + 		
                                        "SEX, " + 			
                                        "EMAIL, " + 		
                                        "ZIPCODE )  values  ( " + 
                                        "'" + client.CLIENTSSN + "', " + 
                                        "'" + client.FIRSTNAME  + "', " + 
                                        "'" + client.MIDDLEINIT	 + "', " + 
                                        "'" + client.LASTNAME	 + "', " + 
                                        "'" + client.STREET	 + "', " + 	
                                        "'" + client.CITY	 + "', " + 		
                                        "'" + client.STATE	 + "', " + 	
                                        "'" + client.PHONE	 + "', " + 	
                                        "'" + client.SEX         + "', " + 	
                                        "'" + client.EMAIL	 + "', " + 	
                                        "'" + client.ZIPCODE	 + "')";	
            System.out.println("Database.insertClient(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            int result = stmt.executeUpdate(sql);

            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.insertClient()" + e);
            throw new AppException("Failed to retrieve client info");

        }
        
    }  //insertClient()
    
    
    public static void updateClient(Client client) throws AppException {

        try {
            Connection conn = getConnection();
        
            String sql = "update client set " +				
                                "FIRSTNAME =	'" + client.FIRSTNAME    + "', " +
                                "MIDDLEINIT =	'" + client.MIDDLEINIT	 + "', " +
                                "LASTNAME =	'" + client.LASTNAME	 + "', " +
                                "STREET =	'" + client.STREET	 + "', " +
                                "CITY =		'" + client.CITY	 + "', " + 	
                                "STATE =	'" + client.STATE	 + "', " + 
                                "PHONE =	'" + client.PHONE	 + "', " +
                                "SEX =		'" + client.SEX		 + "', " +
                                "EMAIL =	'" + client.EMAIL	 + "', " +	
                                "ZIPCODE =	'" + client.ZIPCODE	 + "' " +
                          "where " + 
                                "clientSsn = '" + client.CLIENTSSN + "'";
	
            System.out.println("Database.updateClient(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            int result = stmt.executeUpdate(sql);

            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.updateClient()" + e);
            throw new AppException("Failed to update client info");
        }
        
    }  //updateClient()   
     
    
    public static Client selectClient(String clientSsn) throws AppException  {
        
        Client client = null;
        
        try {
            Connection conn = getConnection();
        
            String sql = "select * from Client where clientSsn = '" + clientSsn + "'";
            System.out.println("Database.selectClient(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            if(rs.next()) { //advance to first record - ASSUMING 1 record
                client = new Client();
                client.CLIENTSSN	= rs.getString("CLIENTSSN");
                client.FIRSTNAME	= rs.getString("FIRSTNAME");
                client.MIDDLEINIT	= rs.getString("MIDDLEINIT");
                client.LASTNAME		= rs.getString("LASTNAME");
                client.STREET		= rs.getString("STREET");
                client.CITY             = rs.getString("CITY");
                client.STATE		= rs.getString("STATE");
                client.PHONE		= rs.getString("PHONE");
                client.SEX              = rs.getString("SEX");
                client.EMAIL		= rs.getString("EMAIL");
                client.ZIPCODE		= rs.getString("ZIPCODE");
            } 
            //else client will be null, indicating no records returned

            rs.close();
            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.selectClient()" + e);
            throw new AppException("Failed to retrieve client info");
        }
        
        return client;
        
    }  //selectClient()

            
                
    public static ArrayList<ScheduledVisit> selectScheduledVisitsForClient(String clientSsn) throws AppException  {
        
       ArrayList<ScheduledVisit> list = new ArrayList<ScheduledVisit>();
        
        try {
            Connection conn = getConnection();
        
            String sql = "select * from ScheduledVisit s, CareGiver c " + 
                         "where s.CLIENTSSN = '" + clientSsn + "'" + 
                         "and c.CAREGIVERID = s.CAREGIVERID";
            System.out.println("Database.selectScheduledVisitsForClient(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            //build list of scheduled visits
            while(rs.next()) {
                ScheduledVisit scheduledVisit = new ScheduledVisit();
                CareGiver careGiver = new CareGiver();
                scheduledVisit.CLIENTSSN 	 	= rs.getString("CLIENTSSN"); 			//VARCHAR2(9 BYTE), 	
                scheduledVisit.DATEOFVISIT 	 	= rs.getString("DATEOFVISIT"); 		//DATE, 
                scheduledVisit.CAREGIVERID 	 	= rs.getString("CAREGIVERID"); 		//NUMBER, 
                scheduledVisit.TYPEOFCARE 		= rs.getString("TYPEOFCARE"); 		//VARCHAR2(100 BYTE), 	
                scheduledVisit.TIMESTARTED 	  	= rs.getString("TIMESTARTED"); 		//DATE, 
                scheduledVisit.TIMEENDED 		= rs.getString("TIMEENDED"); 			//DATE, 
                scheduledVisit.INVOICENUMBER            = rs.getString("INVOICENUMBER");

                careGiver.CAREGIVERID =   rs.getString("CAREGIVERID"); 
                careGiver.FIRSTNAME = 	  rs.getString("FIRSTNAME");
                careGiver.MIDDLEINIT = 	  rs.getString("MIDDLEINIT");
                careGiver.LASTNAME = 	  rs.getString("LASTNAME");
                careGiver.STREET = 		  rs.getString("STREET");
                careGiver.CITY = 		  rs.getString("CITY");
                careGiver.STATE =		  rs.getString("STATE");
                careGiver.ZIPCODE = 	  rs.getString("ZIPCODE");
                careGiver.SEX = 		  rs.getString("SEX");
                careGiver.EMAIL =		  rs.getString("EMAIL");
                careGiver.DATEOFBIRTH =	  rs.getString("DATEOFBIRTH");
                careGiver.PHONE = 		  rs.getString("PHONE");
                careGiver.HOURLYPAYRATE = rs.getString("HOURLYPAYRATE");

                scheduledVisit.careGiver = careGiver;

                //NUMBER
                list.add(scheduledVisit);  
            } 
            //else list will be empty, indicating no records returned

            rs.close();
            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.selectScheduledVisitsForClient()" + e);
            throw new AppException("Failed to retrieve schedule");
        }
        
        return list;
        
    }  //selectScheduledVisitsForClient()

    
    
    public static ArrayList<CaseWorker> selectCaseWorkersForClient(String clientSsn) throws AppException {
        
       ArrayList<CaseWorker> list = new ArrayList<CaseWorker>();
        
        try {
            Connection conn = getConnection();
        
            String sql = "select * from CaseWorker where CASEWORKERID in (" + 
                         "      select CASEWORKERID from Insured where clientSsn = '" + clientSsn + "')";
            System.out.println("Database.selectCaseWorkersForClient(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            //build list of scheduled visits
            while(rs.next()) {
                CaseWorker caseWorker = new CaseWorker();
                caseWorker.CASEWORKERID  	=  rs.getString("CASEWORKERID");
                caseWorker.FIRSTNAME 	 	=  rs.getString("FIRSTNAME");
                caseWorker.MIDDLEINIT 	 	=  rs.getString("MIDDLEINIT");
                caseWorker.LASTNAME 		=  rs.getString("LASTNAME");
                caseWorker.STREET 		=  rs.getString("STREET");
                caseWorker.CITY 		=  rs.getString("CITY");
                caseWorker.STATE 		=  rs.getString("STATE");
                caseWorker.SEX 			=  rs.getString("SEX");
                caseWorker.PHONE 		=  rs.getString("PHONE");
                caseWorker.ZIPCODE              =  rs.getString("ZIPCODE");
                caseWorker.NAMEOFINSURER  	=  rs.getString("NAMEOFINSURER");
                list.add(caseWorker);  
            } 
            //else list will be empty, indicating no records returned

            rs.close();
            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.selectCaseWorkersForClient()" + e);
            throw new AppException("Failed to retrieve Insurance / Caseworker info");
        }
        
        return list;
        
    }  //selectCaseWorkersForClient()


    public static ArrayList<Insured> selectInsuranceForClient(String clientSsn) throws AppException {
        
       ArrayList<Insured> list = new ArrayList<Insured>();
        
        try {
            Connection conn = getConnection();
        
            //note - no need to query actual insurance table, since it's a lookup
            String sql = "select * from Insured i, CaseWorker c " + 
                         "where i.CaseWorkerID = c.CaseWorkerID " + 
                         "  and i.clientSsn = '" + clientSsn + "'";
            System.out.println("Database.selectInsuranceForClient(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            //build list of scheduled visits
            while(rs.next()) {
                Insured insured = new Insured();
                insured.CASEWORKERID            = rs.getString("CASEWORKERID");
                insured.CLIENTSSN               = rs.getString("CLIENTSSN");
                insured.INSURANCENUMBER         = rs.getString("INSURANCENUMBER");
                insured.ISPRIMARY               = rs.getString("ISPRIMARY");
                insured.HOURLYBILLRATE          = rs.getString("HOURLYBILLRATE");
                
                CaseWorker caseWorker = new CaseWorker();
                caseWorker.CASEWORKERID  	=  rs.getString("CASEWORKERID");
                caseWorker.FIRSTNAME 	 	=  rs.getString("FIRSTNAME");
                caseWorker.MIDDLEINIT 	 	=  rs.getString("MIDDLEINIT");
                caseWorker.LASTNAME 		=  rs.getString("LASTNAME");
                caseWorker.STREET 		=  rs.getString("STREET");
                caseWorker.CITY 		=  rs.getString("CITY");
                caseWorker.STATE 		=  rs.getString("STATE");
                caseWorker.SEX 			=  rs.getString("SEX");
                caseWorker.PHONE 		=  rs.getString("PHONE");
                caseWorker.ZIPCODE              =  rs.getString("ZIPCODE");
                caseWorker.NAMEOFINSURER  	=  rs.getString("NAMEOFINSURER");
                
                insured.caseWorker = caseWorker;
                list.add(insured);
                 
            } 
            //else list will be empty, indicating no records returned

            rs.close();
            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.selectInsuranceForClient()" + e);
            throw new AppException("Failed to retrieve Insurance / Caseworker info");
        }
        
        return list;
        
    }  //selectCaseWorkersForClient()
    
    
    public static Insured selectInsured(String clientSsn) throws AppException  {
        
        Insured insured = null;
        
        try {
            Connection conn = getConnection();
        
            String sql = "select * from Insured where clientSsn = '" + clientSsn + "'";
            System.out.println("Database.selectInsured(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            if(rs.next()) { //advance to first record - ASSUMING 1 record
                insured = new Insured();
                insured.CLIENTSSN	= rs.getString("CLIENTSSN");
                insured.CASEWORKERID	= rs.getString("CASEWORKERID");
                insured.INSURANCENUMBER	= rs.getString("INSURANCENUMBER");
                insured.ISPRIMARY	= rs.getString("ISPRIMARY");
                insured.HOURLYBILLRATE	= rs.getString("HOURLYBILLRATE");
            } 
            //else client will be null, indicating no records returned

            rs.close();
            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.selectInsured()" + e);
            throw new AppException("Failed to retrieve insured info");
        }
        
        return insured;
        
    }  //selectInsured()
    
 
   public static void insertInsured(Insured insured) throws AppException {
    
       try {
            Connection conn = getConnection();
        
            String sql = "insert into insured(CLIENTSSN, " +
                                        "CASEWORKERID, " + 
                                        "INSURANCENUMBER, " + 	
                                        "ISPRIMARY, " + 		
                                        "HOURLYBILLRATE )  values  ( " + 
                                        "'" + insured.CLIENTSSN + "', " + 
                                        "'" + insured.CASEWORKERID  + "', " + 
                                        "'" + insured.INSURANCENUMBER	 + "', " + 
                                        "'" + insured.ISPRIMARY	 + "', " + 
                                        "'" + insured.HOURLYBILLRATE	 + "')";	
            System.out.println("Database.insertInsured(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            int result = stmt.executeUpdate(sql);

            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.insertInsured()" + e);
            throw new AppException("Failed to retrieve insured info");

        }
    }//insertInsured

    static void updateInsured(Insured insured) throws AppException  {
         
        try {
            Connection conn = getConnection();
        
            String sql = "update insured set " +				
                                "CASEWORKERID =	'" + insured.CASEWORKERID    + "', " +
                                "INSURANCENUMBER =	'" + insured.INSURANCENUMBER	 + "', " +
                                "ISPRIMARY =	'" + insured.ISPRIMARY	 + "', " +
                                "HOURLYBILLRATE =	'" + insured.HOURLYBILLRATE	 + "' " +
                          "where " + 
                                "clientSsn = '" + insured.CLIENTSSN + "'";
	
            System.out.println("Database.updateInsured(): sql is: " + sql);
            
            Statement stmt = conn.createStatement();
            int result = stmt.executeUpdate(sql);

            stmt.close();
            conn.close();

        } catch (SQLException e) {
            System.out.println("Database.updateInsured()" + e);
            throw new AppException("Failed to update insured info");
        }

    }//updateInsured

    
    
}  //HdcpDatabase
